----------------------------------------------
-- @name: forgotten_hints
-- @author: dion cho
-- @description: test for some important but forgotten hints
----------------------------------------------

@capture_on

-- version
@version

-- create objects
drop table t1 purge;
create table t1(c1 int, c2 int);

create index t1_n1 on t1(c1);
create index t1_n2 on t1(c1, c2);

@gather t1


-------------------------------------------
-- case1: index hint

explain plan for
select /*+ index(t1) */ 
  *
from t1
where c1 = 1 and c2 = 1
;

@plan


explain plan for
select /*+ index(t1 t1_n1) */ 
  *
from t1
where c1 = 1 and c2 = 1
;

@plan


explain plan for
select /*+ index(t1 t1(c1)) */ 
  *
from t1 
where c1 = 1 and c2 = 1
;

@plan


explain plan for
select /*+ index(x x(c1)) */ 
  *
from t1 x
where c1 = 1 and c2 = 1
;

@plan


explain plan for
select /*+ index(x t1(c1)) */ 
  *
from t1 x
where c1 = 1 and c2 = 1
;

@plan


explain plan for
select /*+ index(x t1(c1, c2)) */ 
  *
from t1 x
where c1 = 1 and c2 = 1
;

@plan


explain plan for
select /*+ index(x t1(c1)) */ 
  *
from t1 x
where c1 is not null
;

@plan



---------------------------------------
-- case2: qb_name hint
drop table t1 purge;
drop table t2 purge;

create table t1(c1 int, c2 int);
create table t2(c1 int, c2 int);

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);

@gather t1
@gather t2


explain plan for
select 
  t1.c1, v.c2
from
  t1, 
  (select 
    c1, c2
   from t2
   where c1 between :b1 and :b2) v
where
  t1.c1 = v.c1
;

@plan



explain plan for
select 
  t1.c1, v.c2
from
  t1, 
  (select /*+ full(t2) */
    c1, c2
   from t2
   where c1 between :b1 and :b2) v
where
  t1.c1 = v.c1
;

@plan

explain plan for
select /*+ full(t2) */
  t1.c1, v.c2
from
  t1, 
  (select 
    c1, c2
   from t2
   where c1 between :b1 and :b2) v
where
  t1.c1 = v.c1
;

@plan


explain plan for
select /*+ full(t2) */
  t1.c1, v.c2
from
  t1, 
  (select 
    c1, c2
   from t2
   where c1 between :b1 and :b2) v
where
  t1.c1 = v.c1
;

@plan_all



explain plan for
select /*+ full(@SEL$F5BB74E1 t2) */
  t1.c1, v.c2
from
  t1, 
  (select 
    c1, c2
   from t2
   where c1 between :b1 and :b2) v
where
  t1.c1 = v.c1
;

@plan


explain plan for
select /*+ full(@view1 t2) */
  t1.c1, v.c2
from
  t1, 
  (select /*+ qb_name(view1) */
    c1, c2
   from t2
   where c1 between :b1 and :b2) v
where
  t1.c1 = v.c1
;

@plan


explain plan for
select /*+ full(@view1 t2) */
  t1.c1, v.c2
from
  t1, 
  (select /*+ no_merge qb_name(view1) */
    c1, c2
   from t2
   where c1 between :b1 and :b2) v
where
  t1.c1 = v.c1
;

@plan



-------------------------------------
-- first_rows(k) hint
drop table t1 purge;

create table t1(c1 int, c2 int);

create index t1_n1 on t1(c1)
;

insert into t1
select mod(level, 100)+1, level
from dual
connect by level <= 10000
;

@gather t1

explain plan for
select /*+ full(t1) */
  *
from t1
where c1 = 1
;

@plan


explain plan for
select /*+ index(t1) */
  *
from t1
where c1 = 1
;

@plan


explain plan for
select /*+ first_rows */
  *
from t1
where c1 = 1
;

@plan

explain plan for
select /*+ first_rows(1) */
  *
from t1
where c1 = 1
;

@plan

explain plan for
select /*+ first_rows(10) */
  *
from t1
where c1 = 1
;

@plan

explain plan for
select /*+ first_rows(99) */
  *
from t1
where c1 = 1
;

@plan

explain plan for
select /*+ first_rows(100) */
  *
from t1
where c1 = 1
;

@plan

/*
@ukja102

@trace_on 10053 1

select /*+ first_rows(1) */
  *
from t1
where c1 = 1
;

@trace_off
@trace_file
ho copy &trace_file trc1.out

@ukja102

@trace_on 10053 1

select /*+ first_rows(99) */
  *
from t1
where c1 = 1
;

@trace_off
@trace_file
ho copy &trace_file trc2.out

@diff trc1.out trc2.out

@ukja102

@trace_on 10053 1

select /*+ first_rows(100) */
  *
from t1
where c1 = 1
;

@trace_off
@trace_file
ho copy &trace_file trc3.out

@diff trc2.out trc3.out

*/


@capture_off
